package AbandonedDogPractice;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import AbandonedDogPractice.DogService2;
import AbandonedDogPractice.DogVOPractice;

public class JDBCDogPractice implements DogService2 {
	/* dogNo		num			pk
	 * dName		varchar2	nn
	 * gender		char		nn		sex가 성별구분. gender는 너무 학문적인 개념이라 비추
	 * type			num			fk
	 * age			num			nn
	 * dRegdate	date		nn
	 * collar		num			nn
	 * fno			num			fk
	 * storedLoc			varchar2	????	보관장소 맞지? storedLoc 정도로 일단
	 * etc			varchar2	(비고가 없을 수 있으니 null 허용)
	 * mercykilling	boolean		nn		마찬가지로 mercy killing이 쉬운 표현.
	 * */
	
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	private static final String insertSQL = "insert into Dog (dogNo, dName, sex, type, age, dRegdate, collar, fno, stoerdLoc, ets, mercykilling) values (dogno.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String selectSQL = "select * from Dog order by dogno asc";
	private static final String updateSQL = "update Dog set dname = ?, sex = ?, type = ?, age = ?, dRegdate = ?, collar = ?, fno = ?, storedLoc = ?, etc = ?, mercykilling = ? where dogNo = ?";
	private static final String deleteSql = "delete from Dog where dogNo = ?";
	
	
	@Override
	public boolean newDog(DogVOPractice dg) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, dg.getdName());
				ps.setString(2, dg.getSex());
				ps.setInt(3, dg.getType());
				ps.setInt(4, dg.getAge());
				ps.setString(5, dg.getdRegdate());
				ps.setInt(6, dg.getCollar());
				ps.setInt(7, dg.getFno());
				ps.setString(8, dg.getStoredLoc());
				ps.setString(9, dg.getEtc());
				ps.setBoolean(10, dg.isMercyKilling());
				
				int cnt = ps.executeUpdate();
				if (cnt > 0) { result = true; }
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try { ps.close(); } catch (Exception e) { }
			if (conn != null)
				try { conn.close(); } catch (Exception e) { }
		}
		
		return result;
	}
	@Override
	public String readDog() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				for(int i =1; i <= 11; ++i) {
					result += rs.getString(i) + " ";
				}
				result += "\n";
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) 
			{
				try { rs.close(); } catch (Exception e) { }
			}
			if (ps != null)
			{
				try {ps.close(); } catch (Exception e) { }
			}
			if (conn != null)
			{
				try {conn.close(); } catch (Exception e) { }
			}
		}
		return result;
	}
	@Override
	public boolean modifyDog(DogVOPractice dg) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		return result;
	}
	@Override
	public boolean removeDog(int key) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSql);
			
			int cnt = ps.executeUpdate();
			if (cnt > 0) { result = true; }
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null) {
				try { ps.close(); } catch (Exception e) { }
			}
			if (conn != null) {
				try { conn.close(); } catch (Exception e) { }
			}
		}
		
		return result;
	}
	@Override
	public boolean removeDog(String key) {
		// TODO Auto-generated method stub
		return false;
	}
	

}




